# 数据层 用户信息加载

class UserBasic:
    "用户基本信息"
    def __init__(self,user_no,user_account,user_password,user_type):
        self.no=user_no             # 1用户号
        self.account=user_account   # 2账号
        self.password=user_password # 3密码
        self.type=user_type         # 4用户类型号
    def toString(self):
        return self.no+" "+self.account+" "+self.password+" "+self.type

class Customer:
    "顾客"
    def __init__(self,cus_no,cus_address,cus_credit,cus_balance,cus_discount,cus_expend,cus_user_no,cus_photo,cus_delStatus):
        self.no=cus_no              # 1顾客号
        self.address=cus_address    # 2收货地址
        self.credit=cus_credit      # 3赊购限额
        self.balance=cus_balance    # 4余额
        self.discount=cus_discount  # 5折扣
        self.expend=cus_expend      # 6消费额
        self.user_no=cus_user_no    # 7用户号
        self.photo=cus_photo        # 8顾客头像
        self.delStatus=cus_delStatus# 9删除标识

    def toString(self):
        return "{} {} {} {} {} {} {} {} {}".format(self.no,self.address,self.credit,self.balance,self.discount,self.expend,self.user_no,self.photo,self.delStatus)


class Waiter:
    "客服员"
    def __init__(self,wai_no,wai_turnover,wai_name,wai_user_no,wai_photo,wai_delStatus):
        self.no=wai_no              # 1客服员号
        self.turnover=wai_turnover  # 2营业额
        self.name=wai_name          # 3客服员昵称
        self.user_no=wai_user_no    # 4用户号
        self.photo=wai_photo        # 5头像
        self.delStatus=wai_delStatus# 6删除标识

    def toString(self):
        return "{} {} {} {} {} {}".format(self.no,self.turnover,self.name,self.user_no,self.photo,self.delStatus)

class Host:
    "店主"
    def __init__(self,host_no,host_name,host_user_no,host_photo):
        self.no=host_no                 # 1用户管理员号
        self.name=host_name             # 2用户管理员名称
        self.user_no=host_user_no       # 3用户号
        self.photo = host_photo         # 4头像

    def toString(self):
        return "{} {} {} {}".format(self.no,self.name,self.user_no,self.photo)



## UserBasic的数据操作
# 查找UserBasic,使用account查找
def selUserBasicByAccount(cur,account):
    sqlText = 'select * from userbasic where user_account="{}"'.format(account)
    cur.execute(sqlText)
    result = cur.fetchone()
    if result:
        user = UserBasic(result[0], result[1], result[2], result[3])
        return user
    else:
        return None

def selUserBasicByAccAndPwd(cur,account,pwd):
    sqlText = 'select * from userbasic where user_account="{}"and user_password="{}"'.format(account,pwd)
    cur.execute(sqlText)
    result = cur.fetchone()
    if result:
        user = UserBasic(result[0], result[1], result[2], result[3])
        return user
    else:
        return None

# 修改account
def modiUserBasicAccount(conn,cur,user,account):
    if user:
        sqlText="UPDATE userbasic SET user_account = '{}' WHERE user_no = '{}';".format(account,user.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改password
def modiUserBasicPwd(conn,cur,user,pwd):
    if user:
        sqlText="UPDATE userbasic SET user_password = '{}' WHERE user_no = '{}';".format(pwd,user.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0


## Customer的数据操作
# 新增顾客，主要用于顾客注册，顾客注册时需要（账号，密码，地址，头像）
def addCustomer(conn,cur,account,password,address,photo):
    # cur是游标对象
    # 先判断account是否已经在表中
    if not selUserBasicByAccount(cur,account):
        # 先获得最后一个顾客的主键
        sqlText='select * from customer;'
        cur.execute(sqlText)
        result=cur.fetchall()
        no=len(result)+1

        # 向userbasic表中添加记录
        no_str=str(no).zfill(5)
        user=UserBasic(str(1)+no_str,account,password,1)
        sqlText = "INSERT INTO userbasic VALUES(\'" + str(1) + no_str + "\',\'" + account + "\',\'" + password + "\',1);"
        cur.execute(sqlText)

        # 再向customer中添加记录
        sqlText = "INSERT INTO customer VALUES('" + no_str+ "','"+address+ "',"+"0"+ ","+"0"+ ","+"1"+ ","+"0"+ ",'"+ str(1) + no_str + "','" + photo + "',0);"
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        # account用户名已经存在
        return 0

# 删除顾客
def delCustomer(conn,cur,customer):
    return modiCustomerDelStatus(conn,cur,customer)

# 查找顾客，根据用户号
def selCustomerByUser_no(cur,user_no):
    sqlText = 'select * from customer where cus_user_no="{}";'.format(user_no)
    cur.execute(sqlText)
    result = cur.fetchone()
    if not result:
        return None
    else:
        return Customer(result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8])

# 查找顾客，根据账号
def selCustomerByAcc(cur,account):
    user=selUserBasicByAccount(cur,account)
    if user:
        customer=selCustomerByUser_no(cur,user.no)
        return customer
    else:
        return None

# 查找顾客，根据账号和密码
def selCustomerByAccAndPwd(cur,account,pwd):
    user=selUserBasicByAccount(cur, account)
    if user:
        if user.password==pwd:
            return selCustomerByUser_no(cur,user.no)
        else:
            return None
    else:
        return None

# 查找所有顾客
def selAllCustomers(cur):
    sqlText="select user_account,cus_address,cus_credit,cus_balance,cus_discount,cus_expend,cus_user_no,cus_photo,cus_delStatus from userbasic,customer where userbasic.user_no=customer.cus_user_no;"
    cur.execute(sqlText)
    return cur.fetchall()

# 修改顾客，修改地址
def modiCustomerAddr(conn,cur,customer,address):
    if customer:
        sqlText = "UPDATE customer SET cus_address = '{}' WHERE cus_no = '{}';".format(address,customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改顾客，修改购赊余额
def modiCustomerCredit(conn,cur,customer,credit):
    if customer:
        sqlText = "UPDATE customer SET cus_credit = {} WHERE cus_no = '{}';".format(credit, customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改顾客，修改余额
def modiCustomerBalance(conn,cur,customer,balance):
    if customer:
        sqlText = "UPDATE customer SET cus_balance = {} WHERE cus_no = '{}';".format(balance, customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改顾客，修改折扣
def modiCustomerDiscount(conn,cur,customer,discount):
    if customer:
        sqlText = "UPDATE customer SET cus_discount = {} WHERE cus_no = '{}';".format(discount, customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改顾客，修改花销
def modiCustomerExpend(conn,cur,customer,expend):
    if customer:
        sqlText = "UPDATE customer SET cus_expend = {} WHERE cus_no = '{}';".format(expend, customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改顾客，修改头像
def modiCustomerPhoto(conn,cur,customer,photo):
    if customer:
        sqlText = "UPDATE customer SET cus_photo = '{}' WHERE cus_no = '{}';".format(photo, customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0
    pass

# 修改顾客，修改删除标志
def modiCustomerDelStatus(conn,cur,customer):
    if customer:
        sqlText="UPDATE customer SET cus_delStatus = 1 WHERE cus_no = '{}';".format(customer.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0



## Waiter的数据操作
# 新增客服员
def addWaiter(conn,cur,account,password,name,photo):
    # if not selUserBasicByAccount(cur,account):
    #     # 先获取最后一个客服员的主键
    #     sqlText='select * from waiter;'
    #     cur.execute(sqlText)
    #     result=cur.fetchall()
    #     no=len(result)+1
    #
    #     # 向userbasic中添加记录
    #     no_str=str(no).zfill(5)
    #     user
    if not selUserBasicByAccount(cur,account):
        # 先获得最后一个顾客的主键
        sqlText='select * from waiter;'
        cur.execute(sqlText)
        result=cur.fetchall()
        no=len(result)+1

        # 向userbasic表中添加记录
        no_str=str(no).zfill(5)
        sqlText = "INSERT INTO userbasic VALUES(\'" + str(1) + no_str + "\',\'" + account + "\',\'" + password + "\',2);"
        cur.execute(sqlText)

        # 再向customer中添加记录
        sqlText = "INSERT INTO waiter VALUES('" + no_str+ "',"+str(0)+ ",'"+name+ "','"+str(1) + no_str+ "','"+photo+ "',"+"0);"
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        # account用户名已经存在
        return 0
    pass

# 删除客服员
def delWaiter(conn,cur,waiter):
    return modiWaiterDelStatus(conn,cur,waiter)

# 查找客服员，根据account
def selWaiterByAccount(cur,account):
    user=selUserBasicByAccount(cur,account)
    if user:
        waiter=selWaiterByUser_no(cur,user.no)
        return waiter
    else:
        return None

# 查找客服员，根据user_no
def selWaiterByUser_no(cur,user_no):
    sqlText='select * from waiter where wai_user_no="{}"'.format(user_no)
    cur.execute(sqlText)
    result=cur.fetchone()
    if result:
        # print(result[0])
        # print(result[1])
        # print(result[2])
        # print(result[3])
        # print(result[4])
        # print(result[5])
        waiter=Waiter(result[0],result[1],result[2],result[3],result[4],result[5])
        # waiter.toString()
        return waiter
    else:
        return None

# 查找客服员，根据账号和密码
def selWaiterByAccAndPwd(cur,account,pwd):
    user=selUserBasicByAccount(cur,account)
    if user:
        if user.password==pwd:
            return selWaiterByUser_no(cur,user.no)
        else:
            return None
    else:
        return None

# 查找所有客服员
def selAllWaiters(cur):
    sqlText = "select user_account,wai_name,wai_user_no,wa_photo,wai_delStatus from userbasic,waiter where userbasic.user_no=waiter.wai_user_no;"
    cur.execute(sqlText)
    return cur.fetchall()

# 修改客服员，修改营业额
def modiWaiterTurnover(conn,cur,waiter,turnover):
    if waiter:
        sqlText = "UPDATE waiter SET wai_turnover = {} WHERE wai_no = '{}';".format(turnover, waiter.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改客服员，修改客服员昵称
def modiWaiterName(conn,cur,waiter,name):
    if waiter:
        sqlText = "UPDATE waiter SET wai_name = '{}' WHERE wai_no = '{}';".format(name, waiter.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改客服员，修改头像
def modiWaiterPhoto(conn,cur,waiter,photo):
    if waiter:
        sqlText = "UPDATE waiter SET wai_photo = '{}' WHERE wai_no = '{}';".format(photo,waiter.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改客服员，修改删除标识
def modiWaiterDelStatus(conn,cur,waiter):
    if waiter:
        sqlText = "UPDATE waiter SET wai_delStatus = 1 WHERE wai_no = '{}';".format(waiter.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0



## Host的数据操作
# 获取店主信息
def getHost(cur):
    sqlText="select user_account,host_name,host_user_no,host_photo from userbasic,host where userbasic.user_no=host_user_no;"
    cur.execute(sqlText)
    result=cur.fetchone()
    return Host(result[0],result[1],result[2],result[3])

# 修改店主昵称
def modiHostName(conn,cur,host,name):
    if host:
        sqlText = "UPDATE host SET host_name = '{}' WHERE host_no = '{}';".format(name, host.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 修改头像
def modiHostPhoto(conn,cur,host,photo):
    if host:
        sqlText = "UPDATE host SET host_photo = '{}' WHERE host_no = '{}';".format(photo,host.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0



if __name__ == '__main__':
    print(1+\
      2+\
                3)
    pass
    # conn,cur=connectDB()
    # account="shupeiyao"
    # user_no="100001"
    # # delCustomer(conn,cur,account)
    # # print(selCustomerByUser_no(cur,user_no).no)
    # # print(selCustomerByUser_no(cur,user_no).delStatus)
    # host=getHost(cur)
    # print(host.no)
    # print(host.name)
    # print(host.user_no)
    # print(host.photo)
    # closeDB(cur,conn)